package kaiseki.de.managerwebsite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.vaadin.data.util.IndexedContainer;

public class MySQLData {
	private Connection connection;
	private String password;
	private String username;

	public MySQLData(String user, String pass) {
		this.username = user;
		this.password = pass;
	}

	public void getConnection() throws ClassNotFoundException {

		String url = "jdbc:mysql://localhost:3306/ordersystem?characterEncoding=UTF-8&useUnicode=true";
		String driver = "com.mysql.jdbc.Driver";

		try {
			Class.forName(driver);
			this.connection = DriverManager.getConnection(url, this.username,
					this.password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		}

	}
	
	public List<String> getDrinks() throws SQLException, ClassNotFoundException{
		this.getConnection();
		List<String> drinks = new ArrayList<String>();
		String InsertSQL = "select * from pricetable where t=?";
		PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
		ps.setString(1,"d");
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			String name=rs.getString("name");
			drinks.add(name);
			
		}
		return drinks;
	}
	
	public List<String> getFoods() throws SQLException, ClassNotFoundException{
		this.getConnection();
		List<String> foods = new ArrayList<String>();
		String InsertSQL = "select * from pricetable where t=?";
		PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
		ps.setString(1,"e");
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			String name=rs.getString("name");
			foods.add(name);
			
		}
		return foods;
	}

	public IndexedContainer getPrices()
			throws ClassNotFoundException {
		this.getConnection();
		IndexedContainer ic = new IndexedContainer();
		ic.addContainerProperty("name", String.class, "");
		ic.addContainerProperty("price", String.class, "");
		try {
			double price;
			String InsertSQL = "select * from pricetable";
			PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				price = rs.getDouble("price");
				String idname = rs.getString("name");
				//System.err.print(idname);
				Object id = ic.addItem();
				
				ic.getContainerProperty(id, "name").setValue(idname);
				ic.getContainerProperty(id, "price").setValue(String.valueOf(price));
				//System.err.print(ic.getItem(id).toString());
			}
			//System.err.print(ic.getItem(ic.getIdByIndex(0)).toString());
			return ic;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				this.connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		return ic;

	}
	
	public IndexedContainer getOrders(String status)
			throws ClassNotFoundException {
		int stat;
		if (status.equals("unpay")){
			stat=0;
		}else{
			stat=1;
		}
		this.getConnection();
		IndexedContainer ic = new IndexedContainer();
		ic.addContainerProperty("id", String.class, "");
		ic.addContainerProperty("tablenumber", String.class, "");
		ic.addContainerProperty("item", String.class, "");
		ic.addContainerProperty("cost", String.class, "");
		ic.addContainerProperty("date", String.class, "");
		try {
			double cost;
			int idl;
			int tn;
			String item;
			Date date;
			String InsertSQL = "select * from orders where payed=?";
			PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
			ps.setInt(1,stat);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				idl = rs.getInt("id");
				tn = rs.getInt("tablenumber");
				cost = rs.getDouble("cost");
				item =rs.getString("item");
				date=rs.getDate("date");
			
				Object id = ic.addItem();
				
				ic.getContainerProperty(id, "id").setValue(String.valueOf(idl));
				ic.getContainerProperty(id, "tablenumber").setValue(String.valueOf(tn));
				ic.getContainerProperty(id, "cost").setValue(String.valueOf(cost));
				ic.getContainerProperty(id, "item").setValue(item);
				if(date!=null)
				  ic.getContainerProperty(id, "date").setValue(date.toString());
				else
					 ic.getContainerProperty(id, "date").setValue("none");
				
			}
			//System.err.print(ic.getItem(ic.getIdByIndex(0)).toString());
			return ic;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				this.connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		return ic;

	}
	
	public IndexedContainer getUnPayOrdersFromTable(String table)
			throws ClassNotFoundException {
		
		
		this.getConnection();
		IndexedContainer ic = new IndexedContainer();
		ic.addContainerProperty("id", String.class, "");
		ic.addContainerProperty("tablenumber", String.class, "");
		ic.addContainerProperty("item", String.class, "");
		ic.addContainerProperty("cost", String.class, "");
		ic.addContainerProperty("date", String.class, "");
		try {
			double cost;
			int idl;
			int tn;
			String item;
			Date date;
			String InsertSQL = "select * from orders where payed=? and tablenumber =?";
			PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
			ps.setInt(1,0);
			ps.setInt(2, Integer.parseInt(table));
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				idl = rs.getInt("id");
				tn = rs.getInt("tablenumber");
				cost = rs.getDouble("cost");
				item =rs.getString("item");
				date=rs.getDate("date");
			
				Object id = ic.addItem();
				
				ic.getContainerProperty(id, "id").setValue(String.valueOf(idl));
				ic.getContainerProperty(id, "tablenumber").setValue(String.valueOf(tn));
				ic.getContainerProperty(id, "cost").setValue(String.valueOf(cost));
				ic.getContainerProperty(id, "item").setValue(item);
				if(date!=null)
				  ic.getContainerProperty(id, "date").setValue(date.toString());
				else
					 ic.getContainerProperty(id, "date").setValue("none");
				
			}
			//System.err.print(ic.getItem(ic.getIdByIndex(0)).toString());
			return ic;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				this.connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		return ic;

	}

	public IndexedContainer getOrders(String input,String col)
			throws ClassNotFoundException {
		this.getConnection();
		IndexedContainer ic = new IndexedContainer();
		ic.addContainerProperty("id", String.class, "");
		ic.addContainerProperty("tablenumber", String.class, "");
		ic.addContainerProperty("item", String.class, "");
		ic.addContainerProperty("cost", String.class, "");
		ic.addContainerProperty("date", String.class, "");
		if (col==null){
			col="exception";
		}
		try {
			double cost;
			int idl;
			int tn;
			String item;
			Date date;
			String InsertSQL;
			PreparedStatement ps;
			if(col.equals("id")){
				InsertSQL = "select * from orders where id =?";
				ps = this.connection.prepareStatement(InsertSQL);
				ps.setInt(1, Integer.parseInt(input));
			}else if(col.equals("cost")){
				InsertSQL = "select * from orders where cost like ?";
				ps = this.connection.prepareStatement(InsertSQL);
				ps.setString(1, input);
			}else if (col.equals("tablenumber")){
				InsertSQL = "select * from orders where tablenumber =?";
				ps = this.connection.prepareStatement(InsertSQL);
				ps.setInt(1, Integer.parseInt(input));
			} else if (col.equals("item")){
				InsertSQL = "select * from orders where item =?";
				ps = this.connection.prepareStatement(InsertSQL);
				ps.setString(1, input);
			}else if(col.equals("date")){
				InsertSQL = "select * from orders where date(date) =?";
				ps = this.connection.prepareStatement(InsertSQL);
				ps.setString(1, input);
			}else {
				InsertSQL = "select * from orders ";
				ps = this.connection.prepareStatement(InsertSQL);
				
			}
			
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				idl = rs.getInt("id");
				tn = rs.getInt("tablenumber");
				cost = rs.getDouble("cost");
				item =rs.getString("item");
				date=rs.getDate("date");
			
				Object id = ic.addItem();
				
				ic.getContainerProperty(id, "id").setValue(String.valueOf(idl));
				ic.getContainerProperty(id, "tablenumber").setValue(String.valueOf(tn));
				ic.getContainerProperty(id, "cost").setValue(String.valueOf(cost));
				ic.getContainerProperty(id, "item").setValue(item);
				if(date!=null)
				  ic.getContainerProperty(id, "date").setValue(date.toString());
				else
					 ic.getContainerProperty(id, "date").setValue("none");
				
			}
			//System.err.print(ic.getItem(ic.getIdByIndex(0)).toString());
			return ic;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				this.connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		return ic;

	}
	
	public void deleteOrder(String id) throws NumberFormatException, SQLException, ClassNotFoundException{
		   this.getConnection();
		   String InsertSQL = "delete from orders  where id=?";
           PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
           ps.setInt(1, Integer.parseInt(id));
           ps.execute();
           this.connection.close();
		
	}
	
	public boolean addOrder(String table, String item, String code) throws ClassNotFoundException{
		this.getConnection();
        try {

            String InsertSQL = "insert orders(tablenumber,item,cost,payed) values(?,?,?,?)";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setInt(1, Integer.parseInt(table));
            ps.setString(2, item);
            ps.setDouble(3, this.getPrice(item)*Double.parseDouble(code));
            ps.setInt(4, 0);
            ps.execute();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
		
		
	}
	
	public double getPrice(String name) throws ClassNotFoundException{
		this.getConnection();
        try {
            double price;
            String InsertSQL = "select price from pricetable where name = ?";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setString(1, name);
            ResultSet rs =ps.executeQuery();
            rs.next();
            price=rs.getDouble("price");
            return price;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return 0;

		
	}
	
	public String getCode(String code) throws ClassNotFoundException{
		this.getConnection();
        try {
            String price="1";
            String InsertSQL = "select rate from codetable where code = ?";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setString(1, code);
            ResultSet rs =ps.executeQuery();
            
            if(rs.next());
               price=rs.getString("rate");
            return price;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return "1";

		
	}
	
}
